package com.sdtele.tta.dac;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;

import com.sdtele.tta.models.Doc;
import com.sdtele.tta.models.Task;
import com.sdtele.tta.models.TaskOperation;
import com.sdtele.tta.models.TaskResponse;
import com.sdtele.tta.models.Team;
import com.sdtele.tta.models.User;
import com.sdtele.tta.models.VoteOperation;
import com.sdtele.tta.models.VoteOption;
import com.sdtele.tta.models.VoteScore;
import com.sdtele.tta.models.VoteTask;
import com.sdtele.tta.models.WorkLoad;

public final class Dac {
	private static Dac instance = null;
	private JdbcTemplate jdbcTemplate = null;
	private Properties propSQL = new Properties();

	private Dac() {
	}

	public static Dac getInstance() {
		if (instance == null) {
			instance = new Dac();
		}
		return instance;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public Properties getPropSQL() {
		return propSQL;
	}

	public boolean accoutExist(String account) {
		return accoutExist(account, -1);
	}

	public boolean accoutExist(String account, long excludedId) {
		String sql = propSQL.getProperty("accoutExist");
		return jdbcTemplate.queryForObject(sql, Long.class, account, excludedId) > 0;
	}

	public List<User> allUsers() {
		String sql = propSQL.getProperty("allUsers");
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
	}

	public User getUser(long id) {
		String sql = propSQL.getProperty("getUserById");
		List<User> userList = jdbcTemplate.query(sql, new Object[] { id }, new BeanPropertyRowMapper<User>(User.class));
		if (userList.size() == 1)
			return userList.get(0);
		else
			return null;
	}

	public User getUseroftask(long taskid) {
		String sql = propSQL.getProperty("getUserByItaskd");
		List<User> userList = jdbcTemplate.query(sql, new Object[] { taskid },
				new BeanPropertyRowMapper<User>(User.class));
		if (userList.size() == 1)
			return userList.get(0);
		else
			return null;
	}
	
	public User getUserofVotetask(long taskid) {
		String sql = propSQL.getProperty("getUserByVotetaskd");
		List<User> userList = jdbcTemplate.query(sql, new Object[] { taskid },
				new BeanPropertyRowMapper<User>(User.class));
		if (userList.size() == 1)
			return userList.get(0);
		else
			return null;
	}

	public List<User> getUserbyteamId(long teamid) {
		String sql = propSQL.getProperty("getUserByteamId");
		List<User> userList = jdbcTemplate.query(sql, new Object[] { teamid },
				new BeanPropertyRowMapper<User>(User.class));
		if (!userList.isEmpty())
			return userList;
		else
			return null;
	}
	
	public List<User> getUserbyIsroot(long teamid) {
		String sql = propSQL.getProperty("getUserbyIsroot");
		List<User> userList = jdbcTemplate.query(sql, new Object[] { teamid },
				new BeanPropertyRowMapper<User>(User.class));
		if (!userList.isEmpty())
			return userList;
		else
			return null;
	}

	public List<User> getUserbyimplementId(long taskid) {
		String sql = propSQL.getProperty("getUserByimplementId");
		List<User> userList = jdbcTemplate.query(sql, new Object[] { taskid },
				new BeanPropertyRowMapper<User>(User.class));
		if (!userList.isEmpty())
			return userList;
		else
			return null;
	}

	public List<User> getUserbyreadertId(long taskid) {
		String sql = propSQL.getProperty("getUserByreadertId");
		List<User> userList = jdbcTemplate.query(sql, new Object[] { taskid },
				new BeanPropertyRowMapper<User>(User.class));
		if (!userList.isEmpty())
			return userList;
		else
			return null;
	}

	public User getUser(String account) {
		String sql = propSQL.getProperty("getUserByAccount");
		List<User> userList = jdbcTemplate.query(sql, new Object[] { account },
				new BeanPropertyRowMapper<User>(User.class));
		if (userList.size() == 1)
			return userList.get(0);
		else
			return null;
	}

	public void addUser(User user) {
		try {
			jdbcTemplate.execute(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					String sql = propSQL.getProperty("addUser");
					return conn.prepareStatement(sql, new String[] { "id" });
				}
			}, new PreparedStatementCallback<Integer>() {
				@Override
				public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
					ps.setString(1, user.getAccount());
					ps.setString(2, user.getPasswd());
					ps.setString(3, user.getName());
					ps.setTimestamp(4, user.getCreateTime());
					ps.setLong(5, user.getTeamId());
					ps.setInt(6, user.getIsManager());
					ps.setString(7, user.getPhone());
					ps.setString(8, user.getEmail());
					ps.setString(9, user.getSex());
					ps.setString(10, user.getAvatar());
					int ret = ps.executeUpdate();
					ResultSet rs = ps.getGeneratedKeys();
					if (rs.next())
						user.setId(rs.getLong(1));
					return ret;
				}
			});

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public boolean chgUser(User user) {
		String sql = propSQL.getProperty("chgUser");
		return 1 == jdbcTemplate.update(sql, user.getAccount(), user.getPasswd(), user.getName(), user.getCreateTime(),
				user.getTeamId(), user.getIsManager(), user.getPhone(), user.getEmail(), user.getSex(),
				user.getAvatar(), user.getId());
	}

	public boolean chgUserPasswd(User user) {
		String sql = propSQL.getProperty("chgUserpasswd");
		return 1 == jdbcTemplate.update(sql, user.getPasswd(), user.getId());
	}

	public boolean delUser(long id) {
		String sql = propSQL.getProperty("delUserById");
		return 1 == jdbcTemplate.update(sql, id);
	}

	public boolean delUser(String account) {
		String sql = propSQL.getProperty("delUserByAccount");
		return 1 == jdbcTemplate.update(sql, account);
	}

	public int[] delEmps(long[] ids) {
		String sql = propSQL.getProperty("delUserById");
		List<Object[]> argsList = new ArrayList<Object[]>();
		for (long id : ids)
			argsList.add(new Object[] { id });
		return jdbcTemplate.batchUpdate(sql, argsList);
	}

	public int[] delEmps(String[] accounts) {
		String sql = propSQL.getProperty("delUserByAccount");
		List<Object[]> argsList = new ArrayList<Object[]>();
		for (String account : accounts)
			argsList.add(new Object[] { account });
		return jdbcTemplate.batchUpdate(sql, argsList);
	}

	public List<User> findUserByAccount(String account) {
		String sql = propSQL.getProperty("findUserByAccount");
		return jdbcTemplate.query(sql, new Object[] { account }, new BeanPropertyRowMapper<User>(User.class));
	}

	public User getUser(String account, String passwd) {
		String sql = propSQL.getProperty("getUserByAccountAndPasswd");
		List<User> userList = jdbcTemplate.query(sql, new Object[] { account, passwd },
				new BeanPropertyRowMapper<User>(User.class));
		if (userList.size() == 1)
			return userList.get(0);
		else
			return null;
	}

	public List<Team> allTeam() {
		String sql = propSQL.getProperty("allTeam");
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Team>(Team.class));
	}

	public List<Team> getsubTeams(long teamid) {
		String sql = propSQL.getProperty("getTeamByupid");
		List<Team> teamList = jdbcTemplate.query(sql, new Object[] { teamid },
				new BeanPropertyRowMapper<Team>(Team.class));
		if (!teamList.isEmpty())
			return teamList;
		else
			return null;
	}

	public Team getTeam(long teamid) {
		String sql = propSQL.getProperty("getTeamById");
		List<Team> teamList = jdbcTemplate.query(sql, new Object[] { teamid },
				new BeanPropertyRowMapper<Team>(Team.class));
		if (teamList.size() == 1)
			return teamList.get(0);
		else
			return null;
	}

	public Team getTeam(String TeamName) {
		String sql = propSQL.getProperty("getTeamByName");
		List<Team> teamList = jdbcTemplate.query(sql, new Object[] { TeamName },
				new BeanPropertyRowMapper<Team>(Team.class));
		if (teamList.size() == 1)
			return teamList.get(0);
		else
			return null;
	}

	public void addTeam(Team team) {
		try {
			jdbcTemplate.execute(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					String sql = propSQL.getProperty("addTeam");
					return conn.prepareStatement(sql, new String[] { "id" });
				}
			}, new PreparedStatementCallback<Integer>() {
				@Override
				public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
					ps.setString(1, team.getTeamName());
					ps.setLong(2, team.getUpTeamId());
					ps.setString(3, team.getCityName());
					ps.setLong(4, team.getCityID());

					int ret = ps.executeUpdate();
					ResultSet rs = ps.getGeneratedKeys();
					if (rs.next())
						team.setId(rs.getLong(1));
					return ret;
				}
			});

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public boolean chgTeam(Team team) {
		String sql = propSQL.getProperty("chgTeam");
		return 1 == jdbcTemplate.update(sql, team.getTeamName(), team.getUpTeamId(), team.getCityName(),
				team.getCityID(), team.getId());
	}

	public boolean delTeam(long id) {
		String sql = propSQL.getProperty("delTeamById");
		return 1 == jdbcTemplate.update(sql, id);
	}

	public boolean delTeam(String teamName) {
		String sql = propSQL.getProperty("delTeamByName");
		return 1 == jdbcTemplate.update(sql, teamName);
	}

	public List<Task> allTasks() {
		String sql = propSQL.getProperty("allTasks");
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Task>(Task.class));
	}

	public List<Task> getTask(long userid) {
		String sql = propSQL.getProperty("getTaskById");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}

	public Task getTask(long ownerid, long fathertaskid) {
		String sql = propSQL.getProperty("getTaskByuserId");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { ownerid, fathertaskid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (TaskList.size() == 1)
			return TaskList.get(0);
		else
			return null;
	}

	public Task getTaskbyTaskid(long taskid) {
		String sql = propSQL.getProperty("getTaskByTaskId");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { taskid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (TaskList.size() == 1)
			return TaskList.get(0);
		else
			return null;
	}

	public List<Task> getSubTask(long fatherId) {
		String sql = propSQL.getProperty("getTaskByFatherId");
		List<Task> subTaskList = jdbcTemplate.query(sql, new Object[] { fatherId },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (subTaskList!=null)
			return subTaskList;
		else
			return null;
	}

	public void addTask(Task task) {
		try {
			jdbcTemplate.execute(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					String sql = propSQL.getProperty("addTask");
					return conn.prepareStatement(sql, new String[] { "id" });
				}
			}, new PreparedStatementCallback<Integer>() {
				@Override
				public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
					ps.setLong(1, task.getOwnerId());
					ps.setString(2, task.getTitle());
					ps.setString(3, task.getDescription());
					ps.setTimestamp(4, task.getCreateTime());
					ps.setDate(5, task.getDeadLine());
					ps.setLong(6, task.getEmergency());
					ps.setLong(7, task.getFatherTaskId());
					ps.setLong(8, task.getCompletion());
					ps.setLong(9, task.getState());
					ps.setString(10, task.getTags());
					ps.setTimestamp(11, task.getUpdateTime());
					int ret = ps.executeUpdate();
					ResultSet rs = ps.getGeneratedKeys();
					if (rs.next())
						task.setId(rs.getLong(1));
					return ret;
				}
			});

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public boolean chgTask(Task task) {
		String sql = propSQL.getProperty("chgTask");
		return 1 == jdbcTemplate.update(sql, task.getOwnerId(), task.getTitle(), task.getDescription(),
				task.getDeadLine(), task.getEmergency(), task.getFatherTaskId(), task.getCompletion(), task.getState(),
				task.getTags(), task.getUpdateTime(), task.getId());
	}

	public boolean chgCompletionbyTaskid(Task task) {
		String sql = propSQL.getProperty("chgTaskbyTaskid");
		return 1 == jdbcTemplate.update(sql, task.getCompletion(), task.getUpdateTime(), task.getId());
	}

	public boolean delTask(long id) {
		String sql = propSQL.getProperty("delTaskById");
		return 1 == jdbcTemplate.update(sql, id);
	}
	

	public boolean delTask(String title, long id) {
		String sql = propSQL.getProperty("delTaskByTitle");
		return 1 == jdbcTemplate.update(sql, title, id);

	}

	public TaskOperation getOperation(long taskid, long userid) {
		String sql = propSQL.getProperty("getTaskoperation");
		List<TaskOperation> taskoperationList = jdbcTemplate.query(sql, new Object[] { taskid, userid },
				new BeanPropertyRowMapper<TaskOperation>(TaskOperation.class));
		if (taskoperationList.size() == 1)
			return taskoperationList.get(0);
		else
			return null;
	}

	public List<TaskOperation> getOperation(long taskid) {
		String sql = propSQL.getProperty("getTaskoperationBytaskId");
		List<TaskOperation> taskoperationList = jdbcTemplate.query(sql, new Object[] { taskid },
				new BeanPropertyRowMapper<TaskOperation>(TaskOperation.class));
		if (taskoperationList !=null)
			return taskoperationList;

		else
			return null;
	}

	public void addOperation(TaskOperation task) {
		try {
			jdbcTemplate.execute(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					String sql = propSQL.getProperty("addOperation");
					return conn.prepareStatement(sql, new String[] { "id" });
				}
			}, new PreparedStatementCallback<Integer>() {
				@Override
				public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
					ps.setLong(1, task.getTaskId());
					ps.setLong(2, task.getUserId());
					ps.setLong(3, task.getOperation());
					int ret = ps.executeUpdate();
					ResultSet rs = ps.getGeneratedKeys();
					if (rs.next())
						task.setId(rs.getLong(1));
					return ret;
				}
			});

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public boolean chgTaskoperation(TaskOperation task) {
		String sql = propSQL.getProperty("ChgOperation");
		return 1 == jdbcTemplate.update(sql, task.getUserId(), task.getTaskId(), task.getUserId());
	}

	public boolean delTaskoperation(long taskid) {
		String sql = propSQL.getProperty("delTaskByOpertion");
		return jdbcTemplate.update(sql, taskid)>=0;

	}

	public boolean delTaskoperation(long taskid, long userid) {
		String sql = propSQL.getProperty("delTaskByOpertionandUser");
		return 1 == jdbcTemplate.update(sql, taskid, userid);

	}

	public List<TaskResponse> getResponse(long taskid) {
		String sql = propSQL.getProperty("getTaskResponse");
		List<TaskResponse> responseList = jdbcTemplate.query(sql, new Object[] { taskid },
				new BeanPropertyRowMapper<TaskResponse>(TaskResponse.class));
		if (!responseList.isEmpty())
			return responseList;
		else
			return null;
	}

	public List<TaskResponse> getResponse(long taskid, long userid) {
		String sql = propSQL.getProperty("getTaskResponseUser");
		List<TaskResponse> resList = jdbcTemplate.query(sql, new Object[] { taskid, userid },
				new BeanPropertyRowMapper<TaskResponse>(TaskResponse.class));
		if (!resList.isEmpty())
			return resList;
		else
			return null;
	}
	


	public TaskResponse addResponse(TaskResponse task) {
		try {
			jdbcTemplate.execute(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					String sql = propSQL.getProperty("addResponse");
					return conn.prepareStatement(sql, new String[] { "id" });
				}
			}, new PreparedStatementCallback<Integer>() {
				@Override
				public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
					ps.setLong(1, task.getTaskId());
					ps.setLong(2, task.getUserId());
					ps.setString(3, task.getResponse());
					ps.setTimestamp(4, task.getResponseTime());
					int ret = ps.executeUpdate();
					ResultSet rs = ps.getGeneratedKeys();
					if (rs.next())
						task.setId(rs.getLong(1));
					return ret;
				}
			});

			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return task;
	}

	public boolean chgTaskResponse(TaskResponse task) {
		String sql = propSQL.getProperty("chgResponse");
		return 1 == jdbcTemplate.update(sql, task.getResponse(), task.getResponseTime(), task.getTaskId(),
				task.getUserId());
	}

	public boolean delTaskResponse(long taskid) {
		String sql = propSQL.getProperty("delTaskByResponse");
		return  jdbcTemplate.update(sql, taskid)>=0;

	}
	
	public boolean delTaskResponsebyId(long taskid) {
		String sql = propSQL.getProperty("delTaskByResponsebyId");
		return  jdbcTemplate.update(sql, taskid)>=0;

	}

	public boolean delTaskResponse(long taskid, long userid) {
		String sql = propSQL.getProperty("delTaskByResponseaUser");
		return 1 == jdbcTemplate.update(sql, taskid, userid);

	}

	public void addDoc(Doc doc) {
		try {
			jdbcTemplate.execute(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					String sql = propSQL.getProperty("addDoc");
					return conn.prepareStatement(sql, new String[] { "id" });
				}
			}, new PreparedStatementCallback<Integer>() {
				@Override
				public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
					ps.setLong(1, doc.getTaskId());
					ps.setString(2, doc.getName());
					ps.setString(3, doc.getPath());
					ps.setTimestamp(4, doc.getUpdateTime());
					ps.setLong(5, doc.getUserId());
					ps.setString(6, doc.getRealPath());
					int ret = ps.executeUpdate();
					ResultSet rs = ps.getGeneratedKeys();
					if (rs.next())
						doc.setId(rs.getLong(1));
					return ret;
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public Doc getDoc(long id) {
		String sql = propSQL.getProperty("getDoc");
		List<Doc> docList = jdbcTemplate.query(sql, new Object[] { id }, new BeanPropertyRowMapper<Doc>(Doc.class));
		if (docList.size() == 1)
			return docList.get(0);
		else
			return null;
	}

	public List<Doc> allDocs() {
		String sql = propSQL.getProperty("allDocs");
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Doc>(Doc.class));
	}

	public List<Doc> getDocsOfTask(long taskId) {
		String sql = propSQL.getProperty("getDocsOfTask");
		List<Doc> docList = jdbcTemplate.query(sql, new Object[] { taskId }, new BeanPropertyRowMapper<Doc>(Doc.class));
		if (!docList.isEmpty())
			return docList;
		else
			return null;
	}

	public boolean delDoc(long id) {
		String sql = propSQL.getProperty("delDoc");
		try {
			return 1 == jdbcTemplate.update(sql, id);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean delDocbyTaskid(long taskid) {
		String sql = propSQL.getProperty("delDocbyTaskid");
		try {
			return jdbcTemplate.update(sql, taskid)>=0;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<VoteTask> allVotes() {
		String sql = propSQL.getProperty("allVotess");
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<VoteTask>(VoteTask.class));
	}

	public List<VoteTask> getVoteTask(long userid) {
		String sql = propSQL.getProperty("getVoteTaskById");
		List<VoteTask> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<VoteTask>(VoteTask.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}

	public VoteTask getVoteTaskbyVoteid(long voteid) {
		String sql = propSQL.getProperty("getVoteTaskByvoteId");
		List<VoteTask> TaskList = jdbcTemplate.query(sql, new Object[] { voteid },
				new BeanPropertyRowMapper<VoteTask>(VoteTask.class));
		if (TaskList.size() == 1)
			return TaskList.get(0);
		else
			return null;
	}
	
	

	public void addVoteTask(VoteTask votetask) {
		try {
			jdbcTemplate.execute(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					String sql = propSQL.getProperty("addVoteTask");
					return conn.prepareStatement(sql, new String[] { "id" });
				}
			}, new PreparedStatementCallback<Integer>() {
				@Override
				public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
					ps.setLong(1, votetask.getOwnerId());
					ps.setString(2, votetask.getTitle());
					ps.setString(3, votetask.getDescription());
					ps.setTimestamp(4, votetask.getCreatetime());
					ps.setDate(5, votetask.getDeadLine());
					ps.setInt(6, votetask.getVoteType());
					ps.setString(7, votetask.getCompletion());
					int ret = ps.executeUpdate();
					ResultSet rs = ps.getGeneratedKeys();
					if (rs.next())
						votetask.setId(rs.getLong(1));
					return ret;
				}
			});

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public boolean chgVoteTask(VoteTask votetask) {
		String sql = propSQL.getProperty("chgVoteTask");
		return 1 == jdbcTemplate.update(sql, votetask.getOwnerId(), votetask.getTitle(), votetask.getDescription(),
				votetask.getCreatetime(), votetask.getDeadLine(), votetask.getVoteType(), votetask.getCompletion(),votetask.getId());
	}

	public boolean delVoteTask(long voteId) {
		String sql = propSQL.getProperty("delVoteTaskById");
		return 1 == jdbcTemplate.update(sql, voteId);
	}

	public boolean delVoteTask(String title, long userid) {
		String sql = propSQL.getProperty("delVoteTaskByTitle");
		return 1 == jdbcTemplate.update(sql, title, userid);
	}

	public VoteOperation getVoteOperation(long voteid,long userid) {
		String sql = propSQL.getProperty("getVoteOperation");
		List<VoteOperation> voteList = jdbcTemplate.query(sql, new Object[] { voteid, userid},
				new BeanPropertyRowMapper<VoteOperation>(VoteOperation.class));
		if (voteList.size() == 1)
			return voteList.get(0);
		else
			return null;
	}

	public List<User> getVoteOperationbyImpelement(long voteid) {
		String sql = propSQL.getProperty("getVoteOperationbyImplement");
		List<User> userList = jdbcTemplate.query(sql, new Object[] { voteid },
				new BeanPropertyRowMapper<User>(User.class));
		if (!userList.isEmpty())
			return userList;
		else
			return null;
	}

	public List<User> getVoteOperationbyReader(long voteid) {
		String sql = propSQL.getProperty("getVoteOperationbyReader");
		List<User> userList = jdbcTemplate.query(sql, new Object[] { voteid },
				new BeanPropertyRowMapper<User>(User.class));
		if (!userList.isEmpty())
			return userList;
		else
			return null;
	}

	public User getVoteOperationbyOwner(long voteid) {
		String sql = propSQL.getProperty("getVoteOperationbyOwner");
		List<User> userList = jdbcTemplate.query(sql, new Object[] { voteid },
				new BeanPropertyRowMapper<User>(User.class));
		if (userList.size() == 1)
			return userList.get(0);
		else
			return null;
	}

	public void addVoteOperation(VoteOperation vote) {
		try {
			jdbcTemplate.execute(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					String sql = propSQL.getProperty("addVoteOperation");
					return conn.prepareStatement(sql, new String[] { "id" });
				}
			}, new PreparedStatementCallback<Integer>() {
				@Override
				public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
					ps.setLong(1, vote.getVoteId());
					ps.setLong(2, vote.getUserId());
					ps.setLong(3, vote.getOperation());
					ps.setLong(4, vote.getCompletion());
					int ret = ps.executeUpdate();
					ResultSet rs = ps.getGeneratedKeys();
					if (rs.next())
						vote.setId(rs.getLong(1));
					return ret;
				}
			});

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public boolean chgVoteoperation(VoteOperation vote) {
		String sql = propSQL.getProperty("ChgVoteOperation");
		return 1 == jdbcTemplate.update(sql, vote.getCompletion(), vote.getVoteId(), vote.getUserId());
	}

	public boolean delVoteoperation(long voteid, long userid, long operation) {
		String sql = propSQL.getProperty("delVoteByOpertion");
		return 1 == jdbcTemplate.update(sql, voteid, userid, operation);

	}

	public boolean delVoteoperation(long voteid) {
		String sql = propSQL.getProperty("delVoteByvoteOpertion");
		return  jdbcTemplate.update(sql, voteid)>=0;

	}

	public List<VoteOption> getVoteOption(long voteid) {
		String sql = propSQL.getProperty("getVoteOption");
		List<VoteOption> voteList = jdbcTemplate.query(sql, new Object[] { voteid },
				new BeanPropertyRowMapper<VoteOption>(VoteOption.class));
		if (!voteList.isEmpty())
			return voteList;
		else
			return null;
	}
	
	
	public VoteOption getVoteOptionbyId(long id) {
		String sql = propSQL.getProperty("getVoteOptionbyid");
		List<VoteOption> voteList = jdbcTemplate.query(sql, new Object[] { id },
				new BeanPropertyRowMapper<VoteOption>(VoteOption.class));
		if (voteList.size()==1)
			return voteList.get(0);
		else
			return null;
	}

	public void addVoteOption(VoteOption vote) {
		try {
			jdbcTemplate.execute(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					String sql = propSQL.getProperty("addVoteOption");
					return conn.prepareStatement(sql, new String[] { "id" });
				}
			}, new PreparedStatementCallback<Integer>() {
				@Override
				public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
					ps.setLong(1, vote.getVoteId());
					ps.setLong(2, vote.getVoteOptionId());
					ps.setString(3, vote.getVoteOptionName());
					int ret = ps.executeUpdate();
					ResultSet rs = ps.getGeneratedKeys();
					if (rs.next())
						vote.setId(rs.getLong(1));
					return ret;
				}
			});

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public boolean chgVoteoption(VoteOption vote) {
		String sql = propSQL.getProperty("ChgVoteOption");
		return 1 == jdbcTemplate.update(sql, vote.getVoteOptionName(), vote.getId());
	}

	public boolean delVoteoption(long voteid, long voteoptionid) {
		String sql = propSQL.getProperty("delVoteByOption");
		return 1 == jdbcTemplate.update(sql, voteid, voteoptionid);

	}

	public boolean delVoteoption(long voteid) {
		String sql = propSQL.getProperty("delVoteByvoteOption");
		return jdbcTemplate.update(sql, voteid)>=0;

	}

	public List<VoteScore> getVotescore(long voteid) {
		String sql = propSQL.getProperty("getVoteScore");
		List<VoteScore> votescoreList = jdbcTemplate.query(sql, new Object[] { voteid },
				new BeanPropertyRowMapper<VoteScore>(VoteScore.class));

		if (!votescoreList.isEmpty())
			return votescoreList;
		else
			return null;
	}

	
	public void addVoteScore(VoteScore vote) {
		try {
			jdbcTemplate.execute(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					String sql = propSQL.getProperty("addVoteScore");
					return conn.prepareStatement(sql, new String[] { "id" });
				}
			}, new PreparedStatementCallback<Integer>() {
				@Override
				public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
					ps.setLong(1, vote.getVoteId());
					ps.setLong(2, vote.getUserId());
					ps.setLong(3, vote.getVoteOptionId());
					ps.setLong(4, vote.getScore());
					int ret = ps.executeUpdate();
					ResultSet rs = ps.getGeneratedKeys();
					if (rs.next())
						vote.setId(rs.getLong(1));
					return ret;
				}
			});

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public boolean chgVoteScore(VoteScore vote) {
		String sql = propSQL.getProperty("ChgVoteScore");
		return 1 == jdbcTemplate.update(sql, vote.getScore(), vote.getVoteId());
	}

	public boolean delVoteScore(long voteid) {
		String sql = propSQL.getProperty("delVoteByScore");
		return jdbcTemplate.update(sql, voteid)>=0;
	}

	public List<Task> getTaskbyUser(long userid) {
		String sql = propSQL.getProperty("getNewTask");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}

	public List<Task> getTaskbyDeadline(long userid) {
		String sql = propSQL.getProperty("getTaskbyDeadline");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}

	public List<Task> getTaskbyState(long userid) {
		String sql = propSQL.getProperty("getTaskbyState");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}

	public List<Task> getTaskupDeadline(long userid) {
		String sql = propSQL.getProperty("getTaskupDeadline");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}

	public List<Task> getTaskbyCompletion(long userid) {
		String sql = propSQL.getProperty("getTaskbyCompletion");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}

	public List<Task> getTaskbyWeek(long userid) {
		String sql = propSQL.getProperty("getTaskbyWeek");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}

	public List<Task> getTaskbyReader(long userid) {
		String sql = propSQL.getProperty("getTaskbyReader");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}

	public List<VoteTask> getVote(long userid) {
		String sql = propSQL.getProperty("getVote");
		List<VoteTask> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<VoteTask>(VoteTask.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}

	public List<WorkLoad> getWorkload(long userid) {
		String sql = propSQL.getProperty("getWorkload");
		List<WorkLoad> TaskList = jdbcTemplate.query(sql,
				new Object[] { userid, userid, userid, userid, userid, userid, userid, userid, userid, userid, userid,
						userid, userid, userid, userid, userid, userid, userid, userid, userid, userid,
						userid, userid, userid, userid, userid, userid, userid, userid, userid, 
						userid, userid, userid, userid, userid, userid, userid, userid, userid },
				new BeanPropertyRowMapper<WorkLoad>(WorkLoad.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}

	public List<Task> getTaskofIntime(long userid) {
		String sql = propSQL.getProperty("getTaskofIntime");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}

	public List<Task> getTaskofOuttime(long userid) {
		String sql = propSQL.getProperty("getTaskofOuttime");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}

	public List<Task> getTaskofOuttimesloved(long userid) {
		String sql = propSQL.getProperty("getTaskofOuttimesloved");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (TaskList == null)
			return TaskList;
		else
			return null;
	}

	public List<Task> getTaskofIntimesloved(long userid) {
		String sql = propSQL.getProperty("getTaskofIntimesloved");
		List<Task> TaskList = jdbcTemplate.query(sql, new Object[] { userid },
				new BeanPropertyRowMapper<Task>(Task.class));
		if (!TaskList.isEmpty())
			return TaskList;
		else
			return null;
	}
	
	public List<VoteOperation>  getVoteTaskSizebysloved(long voteId) {
		String sql = propSQL.getProperty("getVoteTaskBysolved");
		List<VoteOperation> OperationList = jdbcTemplate.query(sql, new Object[] { voteId},
				new BeanPropertyRowMapper<VoteOperation>(VoteOperation.class));
		if (!OperationList.isEmpty())
			return OperationList;
		else
			return null;
	}
	public List<VoteOperation>  getVoteTaskSize(long voteId) {
		String sql = propSQL.getProperty("getVoteTaskBysum");
		List<VoteOperation> OperationList = jdbcTemplate.query(sql, new Object[] { voteId },
				new BeanPropertyRowMapper<VoteOperation>(VoteOperation.class));
		if (!OperationList.isEmpty())
			return OperationList;
		else
			return null;

	}
	
	public List<VoteTask>  getVoteOperation(long voteId) {
		String sql = propSQL.getProperty("getMyVoteList");
		List<VoteTask> OperationList = jdbcTemplate.query(sql, new Object[] { voteId},
				new BeanPropertyRowMapper<VoteTask>(VoteTask.class));
		if (!OperationList.isEmpty())
			return OperationList;
		else
			return null;

	}
	
	public List<VoteScore> getVoteScoreByOption(long voteid,long voteoptionid) {
		String sql = propSQL.getProperty("getVoteScoreByOption");
		List<VoteScore> votescoreList = jdbcTemplate.query(sql, new Object[] { voteid,voteoptionid },new BeanPropertyRowMapper<VoteScore>(VoteScore.class));

		if (!votescoreList.isEmpty())
			return votescoreList;
		else
			return null;
	}
	
	public List<VoteScore> getVotescore(long voteid, long userid) {
		String sql = propSQL.getProperty("getVoteScorebyUser");
		List<VoteScore> votescoreList = jdbcTemplate.query(sql, new Object[] { voteid,userid },
				new BeanPropertyRowMapper<VoteScore>(VoteScore.class));
		if (!votescoreList.isEmpty())
			return votescoreList;
		else
			return null;
	}
	
	public  double   getVotesum(long voteid, long voteoptionid) {
		String sql = propSQL.getProperty("getVotesum");
		double   sum = (Double)jdbcTemplate.queryForObject(sql, new Object[] { voteid,voteoptionid },
				java.lang.Double.class);
		return sum;
		
	}
	
	public  double   getVoteavg(long voteid, long voteoptionid) {
		String sql = propSQL.getProperty("getVoteavg");
		double  avg = (Double)jdbcTemplate.queryForObject(sql, new Object[] { voteid,voteoptionid },
				java.lang.Double.class);
		return avg;
		
	}
	
	

}
